import pymysql

# 数据库配置
dbConfig = {
    "host": "gz-cynosdbmysql-grp-1fcte1hx.sql.tencentcdb.com",
    "port": 29903,
    "user": "root",
    "password": "hgd123456.",
    "database": "AITrain"
}

try:
    # 建立数据库连接
    connection = pymysql.connect(
        host=dbConfig["host"],
        port=dbConfig["port"],
        user=dbConfig["user"],
        password=dbConfig["password"],
        database=dbConfig["database"]
    )

    # 创建游标对象
    cursor = connection.cursor()

    # # -------------------------- 新增创建orders表的代码 --------------------------
    # create_table_sql = """
    # CREATE TABLE IF NOT EXISTS orders (
    #     orderid INT PRIMARY KEY AUTO_INCREMENT,
    #     patientid INT NOT NULL,
    #     caregiverid INT DEFAULT NULL,
    #     service_type VARCHAR(50) NOT NULL,
    #     service_time DATETIME NOT NULL,
    #     location VARCHAR(255) NOT NULL,
    #     duration INT NOT NULL,
    #     amount DECIMAL(10,2) NOT NULL,
    #     status ENUM('pending', 'accepted', 'completed', 'cancelled') DEFAULT 'pending',
    #     is_public BOOLEAN DEFAULT FALSE,
    #     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    #     FOREIGN KEY (patientid) REFERENCES patientinformation(id),
    #     FOREIGN KEY (caregiverid) REFERENCES caregiverinformation(caregiverid)
    # )
    # """
    #
    # print("\n正在创建orders表...")
    # cursor.execute(create_table_sql)
    # print("orders表创建成功！")

    # -------------------------- 以下是原有代码 --------------------------
    # 查询AITrain数据库下的所有表
    show_tables_sql = "SHOW TABLES"
    cursor.execute(show_tables_sql)
    tables = cursor.fetchall()
    print("\nAITrain数据库下的表:")
    for table in tables:
        print(table[0])

    #     # ================= 新增代码开始 =================
    # try:
    #     alter_table_sql = """
    #           ALTER TABLE user
    #           ADD COLUMN baidu_face_user_id VARCHAR(255) COMMENT '存储对应百度云人脸库的user_id'
    #           """
    #     print("\n正在为user表添加baidu_face_user_id列...")
    #     cursor.execute(alter_table_sql)
    #     print("baidu_face_user_id列添加成功！")
    #
    # except pymysql.Error as e:
    #     if "Duplicate column name" in str(e):
    #         print("错误: baidu_face_user_id列已存在")
    #     else:
    #         print(f"修改表结构失败: {e}")

    # 查询user表的列信息
    show_columns_sql = "DESCRIBE user"
    cursor.execute(show_columns_sql)
    columns = cursor.fetchall()
    print("\nuser表的列项:")
    for column in columns:
        print(column[0])

    # 查询patientinformation表的列信息
    show_columns_sql = "DESCRIBE patientinformation"
    cursor.execute(show_columns_sql)
    columns = cursor.fetchall()
    print("\npatientinformation表的列项:")
    for column in columns:
        print(column[0])

    # 查询caregiverinformation表的列信息
    show_columns_sql = "DESCRIBE user"
    cursor.execute(show_columns_sql)
    columns = cursor.fetchall()
    print("\nuser表的列项及类型:")
    for column in columns:
        column_name = column[0]
        column_type = column[1]
        print(f"列名: {column_name}, 类型: {column_type}")

    # 修改 patientinformation 表中 time 列的类型为 int
    # alter_table_sql = "ALTER TABLE patientinformation MODIFY COLUMN time INT"
    # cursor.execute(alter_table_sql)
    # print("\npatientinformation 表的 time 列类型已成功修改为 int！")

    show_columns_sql = "DESCRIBE orders"
    cursor.execute(show_columns_sql)
    columns = cursor.fetchall()
    print("\norders表的列项及类型:")
    for column in columns:
        column_name = column[0]
        column_type = column[1]
        print(f"列名: {column_name}, 类型: {column_type}")

    # 查询caregiverinformation表的列信息
    show_columns_sql = "DESCRIBE patientinformation"
    cursor.execute(show_columns_sql)
    columns = cursor.fetchall()
    print("\norders表的列项:")
    for column in columns:
        print(column[0])

    #     # ================= 新增代码开始 =================
    # try:
    #     alter_table_sql = """
    #        ALTER TABLE user
    #        ADD COLUMN face_embedding BLOB COMMENT '存储人脸特征向量'
    #        """
    #     print("\n正在为user表添加face_embedding列...")
    #     cursor.execute(alter_table_sql)
    #     print("face_embedding列添加成功！")
    #
    # except pymysql.Error as e:
    #     if "Duplicate column name" in str(e):
    #         print("错误: face_embedding列已存在")
    #     else:
    #         print(f"修改表结构失败: {e}")

    # 查询caregiverinformation表的详细用户信息
    select_all_info_sql = "SELECT * FROM user"
    cursor.execute(select_all_info_sql)
    caregiver_info = cursor.fetchall()
    print("\ncaregiverinformation表的详细用户信息:")
    for info in caregiver_info:
        print(info)
    # 查询caregiverinformation表的详细用户信息
    select_all_info_sql = "SELECT * FROM patientinformation"
    cursor.execute(select_all_info_sql)
    caregiver_info = cursor.fetchall()
    print("\npatientinformation表的详细用户信息:")
    for info in caregiver_info:
        print(info)


except pymysql.Error as e:
    print(f"\n数据库操作出错: {e}")
finally:
    # 关闭游标和连接
    if cursor:
        cursor.close()
    if connection:
        connection.close()